import os
import pandas as pd
import mysql.connector

basic_path = str(os.path.dirname(__file__)) + "\\"
grade_major_path = basic_path + "..\\simer\\sim\\school_grade.csv"
from simer.grade_simer import init

def init_grade_plan(recreate=False):
    con = mysql.connector.connect(
        host="192.168.80.190",
        port=3306,
        user="hive",
        password="12345678",
        database="gaokao"
        # host="127.168.80.190",
        # port=3306,
        # user="root",
        # password="12345678",
        # database="gaokao",
        # use_pure=True,
        # ssl_disabled=True
    )
    cursor = con.cursor()
    columns = ["province_name", "school_name", "year", "major_name", "batch_name", "type_name", "level1_name",
               "level2_name", "level3_name", "special_code", "require_json", "require_str", "num_student", "max_grade",
               "min_grade", "min_rank", "avg_grade", "is_plan", "is_grade"]
    if recreate:
        drop_query = "DROP TABLE IF EXISTS grade_plan"
        cursor.execute(drop_query)
        create_query = "CREATE TABLE grade_plan " \
                       "(province_name VARCHAR(20) NOT NULL," \
                       " school_name VARCHAR(50) NOT NULL ," \
                       " year VARCHAR(10) NOT NULL ," \
                       " major_name TEXT," \
                       " batch_name VARCHAR(255)," \
                       " type_name VARCHAR(255)," \
                       " level1_name VARCHAR(100)," \
                       " level2_name VARCHAR(100)," \
                       " level3_name VARCHAR(100)," \
                       " special_code VARCHAR(50)," \
                       " require_json VARCHAR(100)," \
                       " require_str VARCHAR(100)," \
                       " num_student INT default 0," \
                       " max_grade INT default 0," \
                       " min_grade INT default 0," \
                       " min_rank INT default 0," \
                       " avg_grade INT default 0," \
                       " is_plan INT default 0," \
                       " is_grade INT default 0)"
        cursor.execute(create_query)
        index_query = "create index province_school_year on grade_plan(province_name,school_name,year)"
        cursor.execute(index_query)
        con.commit()
    df = pd.read_csv(grade_major_path, encoding="utf-8-sig")
    insert_query = "INSERT INTO grade_plan (province_name, school_name, year, major_name, batch_name, type_name, level1_name, level2_name, level3_name, special_code, require_json, require_str, num_student, max_grade, min_grade, min_rank, avg_grade, is_plan, is_grade) " \
                   "VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s','%s', %d, %d, %d, %d, %d, %d, %d)"
    for i in df.iterrows():
        row = i[1]
        major_name = str(row["major_name"]).replace('\'', "").replace(',', "")
        batch_name = str(row["batch_name"]).replace('\'', "").replace(',', "")
        type_name = str(row["type_name"]).replace('\'', "").replace(',', "")
        level1_name = str(row["level1_name"]).replace('\'', "").replace(',', "")
        level2_name = str(row["level2_name"]).replace('\'', "").replace(',', "")
        level3_name = str(row["level3_name"]).replace('\'', "").replace(',', "")
        q = insert_query % (
            row["province_name"], row["school_name"], row["year"], major_name, batch_name, type_name, level1_name,
            level2_name, level3_name, row["special_code"], row["require_json"],
            row["require_str"], row["num_student"], row["max_grade"], row["min_grade"], row["min_rank"],
            row["avg_grade"], row["is_plan"], row["is_grade"])
        q = q.replace("nan", "")
        try:
            cursor.execute(q)
        except Exception as a:
            print(str(a)+'\n'+q)
        con.commit()
    cursor.close()
    con.close()

init_grade_plan(True)